{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 06 炒菜计时器-时间序列"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "from datetime import datetime\n",
    "# 一个cell输出多行语句\n",
    "from IPython.core.interactiveshell import InteractiveShell\n",
    "InteractiveShell.ast_node_interactivity = \"all\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 一、获取当前时刻的时间\n",
    "获取当前时刻的时间就是获取**此时此刻与时间相关**的数据\n",
    "\n",
    "除了具体的**年、月、日、时、分、秒**，还会单独看**年、月、周、日**等指标\n",
    "\n",
    "### 1.1 返回当前时刻的日期与时间\n",
    "Excel和Python实现：**now()**函数"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "datetime.datetime(2020, 3, 4, 10, 14, 37, 497992)"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "datetime.now()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1.2 返回当前时刻的年、月、日\n",
    "Excel和Python实现：**year**、**month**、**day**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2020"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "3"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "4"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "datetime.now().year\n",
    "datetime.now().month\n",
    "datetime.now().day"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1.3 返回当前时刻的周数\n",
    "与当前时刻的周相关的数据有两个：\n",
    "\n",
    "* 当前时刻是一周中的周几\n",
    "* 当前所在的周在全年的周里面是第几周\n",
    "\n",
    "#### 1.3.1 返回周几\n",
    "Excel实现：**weekday(now() - 1)**函数，减1是因为Excel把周日作为一周中的第一天\n",
    "\n",
    "python实现：**weekday()**函数"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "3"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "datetime.now().weekday() + 1\n",
    "# python从0开始计算，周日返回6，所以在后面加1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 1.3.2 返回周数\n",
    "\n",
    "Excel实现：**weeknum(now() - 1)**函数\n",
    "\n",
    "python实现：**isocalendar()**函数"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(2020, 10, 3)"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "10"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "datetime.now().isocalendar()\n",
    "# 2020年，第10周，第2天（周二）\n",
    "datetime.now().isocalendar()[1]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 二、指定日期和时间的格式\n",
    "\n",
    "Excel实现：右键-》设置单元格格式"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "datetime.date(2020, 3, 4)"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "datetime.time(10, 14, 37, 529903)"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "datetime.now().date()\n",
    "datetime.now().time()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "借助**strftime()**函数自定义日期和时间的格式\n",
    "\n",
    "<img src='./image/strftime.jpg' width='80%' />"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'2020-03-04'"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "'2020-03-04 10:14:37'"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "datetime.now().strftime('%Y-%m-%d')\n",
    "datetime.now().strftime('%Y-%m-%d %H:%M:%S')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 三、字符串和时间格式相互转换\n",
    "\n",
    "### 3.1 将时间格式转换为字符串格式"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "datetime.datetime(2020, 3, 4, 10, 14, 37, 542143)"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "datetime.datetime"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "str"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "now = datetime.now()\n",
    "now\n",
    "type(now)\n",
    "type(str(now))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.2 将字符串格式转换为时间格式"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "str"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "datetime.datetime(2020, 3, 3, 0, 0)"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "datetime.datetime"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from dateutil.parser import parse\n",
    "str_now = '2020-03-03'\n",
    "type(str_now)\n",
    "\n",
    "# 将字符串解析为时间\n",
    "parse(str_now)\n",
    "type(parse(str_now))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 四、时间索引\n",
    "时间索引是根据时间来对**时间格式的字段**进行**数据选取**的一种索引方式\n",
    "\n",
    "Excel实现：**日期筛选**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>num</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-01-02</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-01-03</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-01-04</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-01-05</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-01-06</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-01-07</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-01-08</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-01-09</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-01-10</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            num\n",
       "2020-01-01    1\n",
       "2020-01-02    2\n",
       "2020-01-03    3\n",
       "2020-01-04    4\n",
       "2020-01-05    5\n",
       "2020-01-06    6\n",
       "2020-01-07    7\n",
       "2020-01-08    8\n",
       "2020-01-09    9\n",
       "2020-01-10   10"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "index = pd.DatetimeIndex(['2020-01-01', '2020-01-02', '2020-01-03', \n",
    "                          '2020-01-04', '2020-01-05', '2020-01-06', \n",
    "                          '2020-01-07', '2020-01-08', '2020-01-09', \n",
    "                          '2020-01-10'])\n",
    "data = pd.DataFrame(np.arange(1, 11), columns=['num'], index=index)\n",
    "data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>num</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-01-02</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-01-03</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-01-04</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-01-05</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-01-06</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-01-07</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-01-08</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-01-09</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-01-10</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            num\n",
       "2020-01-01    1\n",
       "2020-01-02    2\n",
       "2020-01-03    3\n",
       "2020-01-04    4\n",
       "2020-01-05    5\n",
       "2020-01-06    6\n",
       "2020-01-07    7\n",
       "2020-01-08    8\n",
       "2020-01-09    9\n",
       "2020-01-10   10"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>num</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-01-02</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-01-03</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-01-04</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-01-05</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-01-06</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-01-07</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-01-08</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-01-09</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-01-10</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            num\n",
       "2020-01-01    1\n",
       "2020-01-02    2\n",
       "2020-01-03    3\n",
       "2020-01-04    4\n",
       "2020-01-05    5\n",
       "2020-01-06    6\n",
       "2020-01-07    7\n",
       "2020-01-08    8\n",
       "2020-01-09    9\n",
       "2020-01-10   10"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>num</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-01-02</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-01-03</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-01-04</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-01-05</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            num\n",
       "2020-01-01    1\n",
       "2020-01-02    2\n",
       "2020-01-03    3\n",
       "2020-01-04    4\n",
       "2020-01-05    5"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>num</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            num\n",
       "2020-01-01    1"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 获取2020年的数据\n",
    "data['2020']\n",
    "# 获取2020年1月的数据\n",
    "data['2020-01']\n",
    "# 获取2020年1月1日到2020年1月5日的数据，包含1月1日和1月5日\n",
    "data['2020-01-01':'2020-01-05']\n",
    "# 获取2020年1月1日的数据\n",
    "data['2020-01-01':'2020-01-01']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>订单编号</th>\n",
       "      <th>客户姓名</th>\n",
       "      <th>唯一识别码</th>\n",
       "      <th>年龄</th>\n",
       "      <th>成交时间</th>\n",
       "      <th>销售ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>A1</td>\n",
       "      <td>张 通</td>\n",
       "      <td>101</td>\n",
       "      <td>31</td>\n",
       "      <td>2018-08-08</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>A2</td>\n",
       "      <td>李谷</td>\n",
       "      <td>102</td>\n",
       "      <td>45</td>\n",
       "      <td>2018-08-09</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>A3</td>\n",
       "      <td>孙凤</td>\n",
       "      <td>103</td>\n",
       "      <td>23</td>\n",
       "      <td>2018-08-10</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>A4</td>\n",
       "      <td>赵恒</td>\n",
       "      <td>104</td>\n",
       "      <td>240</td>\n",
       "      <td>2018-08-11</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>A5</td>\n",
       "      <td>赵恒</td>\n",
       "      <td>104</td>\n",
       "      <td>260</td>\n",
       "      <td>2018-08-12</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>A6</td>\n",
       "      <td>王丹</td>\n",
       "      <td>105</td>\n",
       "      <td>280</td>\n",
       "      <td>2018-08-12</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  订单编号 客户姓名  唯一识别码   年龄       成交时间  销售ID\n",
       "0   A1  张 通    101   31 2018-08-08     1\n",
       "1   A2   李谷    102   45 2018-08-09     2\n",
       "2   A3   孙凤    103   23 2018-08-10     1\n",
       "3   A4   赵恒    104  240 2018-08-11     2\n",
       "4   A5   赵恒    104  260 2018-08-12     3\n",
       "5   A6   王丹    105  280 2018-08-12     4"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>订单编号</th>\n",
       "      <th>客户姓名</th>\n",
       "      <th>唯一识别码</th>\n",
       "      <th>年龄</th>\n",
       "      <th>成交时间</th>\n",
       "      <th>销售ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>A1</td>\n",
       "      <td>张 通</td>\n",
       "      <td>101</td>\n",
       "      <td>31</td>\n",
       "      <td>2018-08-08</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  订单编号 客户姓名  唯一识别码  年龄       成交时间  销售ID\n",
       "0   A1  张 通    101  31 2018-08-08     1"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>订单编号</th>\n",
       "      <th>客户姓名</th>\n",
       "      <th>唯一识别码</th>\n",
       "      <th>年龄</th>\n",
       "      <th>成交时间</th>\n",
       "      <th>销售ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>A3</td>\n",
       "      <td>孙凤</td>\n",
       "      <td>103</td>\n",
       "      <td>23</td>\n",
       "      <td>2018-08-10</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>A4</td>\n",
       "      <td>赵恒</td>\n",
       "      <td>104</td>\n",
       "      <td>240</td>\n",
       "      <td>2018-08-11</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>A5</td>\n",
       "      <td>赵恒</td>\n",
       "      <td>104</td>\n",
       "      <td>260</td>\n",
       "      <td>2018-08-12</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>A6</td>\n",
       "      <td>王丹</td>\n",
       "      <td>105</td>\n",
       "      <td>280</td>\n",
       "      <td>2018-08-12</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  订单编号 客户姓名  唯一识别码   年龄       成交时间  销售ID\n",
       "2   A3   孙凤    103   23 2018-08-10     1\n",
       "3   A4   赵恒    104  240 2018-08-11     2\n",
       "4   A5   赵恒    104  260 2018-08-12     3\n",
       "5   A6   王丹    105  280 2018-08-12     4"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>订单编号</th>\n",
       "      <th>客户姓名</th>\n",
       "      <th>唯一识别码</th>\n",
       "      <th>年龄</th>\n",
       "      <th>成交时间</th>\n",
       "      <th>销售ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>A1</td>\n",
       "      <td>张 通</td>\n",
       "      <td>101</td>\n",
       "      <td>31</td>\n",
       "      <td>2018-08-08</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>A2</td>\n",
       "      <td>李谷</td>\n",
       "      <td>102</td>\n",
       "      <td>45</td>\n",
       "      <td>2018-08-09</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  订单编号 客户姓名  唯一识别码  年龄       成交时间  销售ID\n",
       "0   A1  张 通    101  31 2018-08-08     1\n",
       "1   A2   李谷    102  45 2018-08-09     2"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>订单编号</th>\n",
       "      <th>客户姓名</th>\n",
       "      <th>唯一识别码</th>\n",
       "      <th>年龄</th>\n",
       "      <th>成交时间</th>\n",
       "      <th>销售ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>A2</td>\n",
       "      <td>李谷</td>\n",
       "      <td>102</td>\n",
       "      <td>45</td>\n",
       "      <td>2018-08-09</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>A3</td>\n",
       "      <td>孙凤</td>\n",
       "      <td>103</td>\n",
       "      <td>23</td>\n",
       "      <td>2018-08-10</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  订单编号 客户姓名  唯一识别码  年龄       成交时间  销售ID\n",
       "1   A2   李谷    102  45 2018-08-09     2\n",
       "2   A3   孙凤    103  23 2018-08-10     1"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 以上方法适用于索引是时间的情况下。当时并不是所有情况，时间都可以做索引。\n",
    "# 比如：一个订单表中的客户姓名是索引，成交时间是一个普通列。\n",
    "# 解决办法：时间也是有大小关系的，通过布尔索引来对非索引列的时间进行选取。\n",
    "df = pd.read_excel('./data/sort_one.xlsx')\n",
    "df\n",
    "# 选取成交时间在2018年8月8日的订单\n",
    "df[df['成交时间'] == datetime(2018, 8, 8)]\n",
    "# 8月9日之后的订单\n",
    "df[df['成交时间'] > datetime(2018, 8, 9)]\n",
    "# 8月10日之前的订单\n",
    "df[df['成交时间'] < datetime(2018, 8, 10)]\n",
    "# 8月8日到8月11日之间的订单\n",
    "df[(df['成交时间'] > datetime(2018, 8, 8)) & (df['成交时间'] < datetime(2018, 8, 11))]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 五、时间运算\n",
    "### 5.1 两个时间之差\n",
    "计算时间之差的场景：计算一个用户在某个平台上的生命周期=用户最后一次登录时间-用户首次登录时间\n",
    "\n",
    "Excel实现\n",
    "\n",
    "<img src='./image/excel_time_diff.jpg' width='60%' />"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "datetime.timedelta(days=2, seconds=83880)"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "2"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "83880"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "23.3"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cha = datetime(2020, 5, 21, 19, 50) - datetime(2020, 5, 18, 20, 32)\n",
    "cha\n",
    "cha.days\n",
    "cha.seconds\n",
    "# 小时\n",
    "cha.seconds/3600"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 5.1 时间偏移\n",
    "时间偏移是将时间往前推或往后推一段时间，即**加减一段时间**\n",
    "\n",
    "Excel实现\n",
    "\n",
    "<img src='./image/excel_time_offset.jpg' width='60%' />"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* Python实现一：timedelta，但是只支持偏移天、秒、微妙单位的时间\n",
    "* Python实现二：Pandas的偏移量（date offset）\n",
    "\n",
    "#### 5.1.1 timedelta\n",
    "只支持天、秒、微妙单位的时间运算，如果是其他时间，需要换算成以上三种中的一种方可进行偏移"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "datetime.datetime(2020, 5, 19, 20, 32)"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "datetime.datetime(2020, 5, 18, 20, 33)"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "datetime.datetime(2020, 5, 17, 20, 32)"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "datetime.datetime(2020, 5, 18, 20, 31)"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from datetime import timedelta\n",
    "date = datetime(2020, 5, 18, 20, 32)\n",
    "# 往后推1天\n",
    "date + timedelta(days=1)\n",
    "# 往后推60秒\n",
    "date + timedelta(seconds=60)\n",
    "# 往前推1天\n",
    "date - timedelta(days=1)\n",
    "# 往前推60秒\n",
    "date - timedelta(seconds=60)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 5.1.2 date offset\n",
    "date offset可直接实现天、小时、分钟单位的时间偏移，不需要换算，相比timedelta要方便些"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Timestamp('2020-05-19 20:32:00')"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "Timestamp('2020-05-18 21:32:00')"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "Timestamp('2020-05-18 20:33:00')"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "Timestamp('2020-05-17 20:32:00')"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "Timestamp('2020-05-18 19:32:00')"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "Timestamp('2020-05-18 20:31:00')"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from pandas.tseries.offsets import Day,Hour,Minute\n",
    "date = datetime(2020, 5, 18, 20, 32)\n",
    "\n",
    "# 往后推1天\n",
    "date + Day(1)\n",
    "# 往后推1小时\n",
    "date + Hour(1)\n",
    "# 往后推10分钟\n",
    "date + Minute(1)\n",
    "# 往前推1天\n",
    "date - Day(1)\n",
    "# 往前推1小时\n",
    "date - Hour(1)\n",
    "# 往前推10分钟\n",
    "date - Minute(1)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
